package org.huel.backend.mapper;

import org.apache.ibatis.annotations.*;
import org.huel.backend.domain.dto.StudentScoreDTO;

import java.util.List;
import java.util.Map;

@Mapper
public interface StudentScoreMapper {

    @Select({
            "SELECT student_name, student_id, class_id, ",
            "SUM(score) as total_score, ",
            "SUM(CASE WHEN is_absent = 0 THEN 1 ELSE 0 END) as attendance_count, ",
            "SUM(CASE WHEN is_absent = 1 THEN 1 ELSE 0 END) as absent_count, ",
            "(SELECT course_name FROM attendance a2 ",
            " WHERE a2.student_id = a1.student_id ",
            " GROUP BY course_name ORDER BY COUNT(*) DESC LIMIT 1) as course_name ",
            "FROM attendance a1 ",
            "GROUP BY student_name, student_id, class_id ",
            "ORDER BY total_score DESC ",
            "LIMIT #{limit}"
    })
    @Results({
            @Result(property = "studentName", column = "student_name"),
            @Result(property = "studentId", column = "student_id"),
            @Result(property = "classId", column = "class_id"),
            @Result(property = "totalScore", column = "total_score"),
            @Result(property = "attendanceCount", column = "attendance_count"),
            @Result(property = "absentCount", column = "absent_count"),
            @Result(property = "courseName", column = "course_name")
    })
    List<StudentScoreDTO> findTopStudentsByScore(@Param("limit") int limit);

    @Select({
            "SELECT COUNT(*) as total, ",
            "SUM(CASE WHEN is_absent = 0 THEN 1 ELSE 0 END) as present_count, ",
            "SUM(CASE WHEN is_absent = 1 THEN 1 ELSE 0 END) as absent_count ",
            "FROM attendance"
    })
    @Results({
            @Result(property = "totalCount", column = "total"),
            @Result(property = "presentCount", column = "present_count"),
            @Result(property = "absentCount", column = "absent_count")
    })
    Map<String, Object> getAttendanceSummary();
}